﻿using System;
using System.Data;
using YouErYuanModels;
using CommonLib;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace YouErYuanBLL
{

    public class TeacherBLL
    {

        public Teacher getTeacher(string userName, string password)
        {
            string sql = "SELECT [id],[teacherName],[kahao],[password],[sex],[phone],[address],[bornDay],[headImgURL],[schoolId],[type],[status] FROM [dbo].[T_Teacher] where [phone]=@phone and [password]=@password";
            string pwd_bm = DESencodingTxt.DESencrypt(password);
            SqlParameter[] parames =
            {
                new SqlParameter("@phone",userName),
                new SqlParameter("@password", pwd_bm)
            };
            DataTable dt = SQLHepler.GetDataSet(sql, parames).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                var item = dt.Rows[0];
                Teacher model = new Teacher();
                model.Id = item["id"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["id"]);
                model.Teachername = item["Teachername"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["Teachername"]);
                model.Kahao = item["kahao"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["kahao"]);
                model.Sex = item["sex"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["sex"]);
                model.Phone = item["phone"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["phone"]);
                model.Address = item["address"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["address"]);
                model.Bornday = item["bornDay"].GetType() == typeof(System.DBNull) ? new Nullable<DateTime>() : Convert.ToDateTime(item["bornDay"]);
                model.Headimgurl = item["headImgURL"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["headImgURL"]);
                model.Schoolid = item["schoolId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["schoolId"]);
                model.Type = item["type"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["type"]);
                model.password = item["password"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["password"]);
                model.status = item["status"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["status"]);
                return model;
            }
            return null;
        }

        /// <summary>
        /// 验证教师账号是否存在，存在返回1，不存在返回0
        /// </summary>
        /// <param name="phone"></param>
        /// <returns></returns>
        public int CheckTeacherPhone(string phone)
        {
            string sql = "SELECT COUNT(1) FROM [dbo].[T_Teacher] where [phone]=@phone";
            object obj = SQLHepler.GetSingleValue(sql, new SqlParameter("@phone", phone));
            if (obj != null)
            {
                return Convert.ToInt32(obj);
            }
            return 0;
        }

        /// <summary>
        /// 验证管理员账号是否存在，存在返回1，不存在返回0
        /// </summary>
        /// <param name="phone"></param>
        /// <returns></returns>
        public int CheckTeacherPhone(string phone, string type)
        {
            string sql = "SELECT COUNT(1) FROM [dbo].[T_Teacher] where [phone]=@phone and type=@type";
            SqlParameter[] parames = {
                new SqlParameter("@phone",phone),
                new SqlParameter("@type",type)
            };
            object obj = SQLHepler.GetSingleValue(sql, parames);
            if (obj != null)
            {
                return Convert.ToInt32(obj);
            }
            return 0;
        }

        /// <summary>
        /// 根据老师id查询信息
        /// </summary>
        /// <param name="teacherId"></param>
        /// <returns></returns>
        public object GetTeacherByName(string teacherName, string schoolid)
        {
            string sql = "SELECT [id],[teacherName],[kahao],[password],[sex],[phone],[address],[bornDay],[headImgURL],[schoolId],[type],[status] FROM [YouErYuanDB].[dbo].[T_Teacher] WHERE schoolid=@schoolid and teacherName =@name";
            DataTable dt = SQLHepler.GetDataTable(sql, new SqlParameter[] { new SqlParameter("@schoolid", schoolid), new SqlParameter("@name", teacherName) });
            return dt;
        }

        /// <summary>
        /// 根据老师id查询信息
        /// </summary>
        /// <param name="teacherId"></param>
        /// <returns></returns>
        public object GetTeacherForId(string teacherid)
        {
            string sql = "SELECT * from v_teacher WHERE id=@teacherid";
            DataTable dt = SQLHepler.GetDataTable(sql, new SqlParameter[] { new SqlParameter("@teacherid", teacherid) });
            if (dt != null && dt.Rows.Count > 0)
            {
                string pwd_old = dt.Rows[0]["password"].GetType() == typeof(DBNull) ? "" : Convert.ToString(dt.Rows[0]["password"]);
                try
                {
                    dt.Rows[0]["password"] = CommonLib.DESencodingTxt.DESDecoding(pwd_old);
                }
                catch (Exception)
                {
                    dt.Rows[0]["password"] = "123456";
                }

            }
            return dt;
        }
        /// <summary>
        /// 根据老师id查询信息
        /// </summary>
        /// <param name="teacherId"></param>
        /// <returns></returns>
        public Teacher GetTeacherById(string teacherId)
        {
            string sql = "SELECT [id],[teacherName],[kahao],[password],[sex],[phone],[address],[bornDay],[headImgURL],[schoolId],[type],[status] FROM [YouErYuanDB].[dbo].[T_Teacher] WHERE [id]=@id";
            DataTable dt = SQLHepler.GetDataSet(sql, new SqlParameter("@id", teacherId)).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                Teacher model = new Teacher();
                var itme = dt.Rows[0];
                model.Id = itme["id"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(itme["id"]);
                model.Teachername = itme["teacherName"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(itme["teacherName"]);
                model.Kahao = itme["kahao"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(itme["kahao"]);
                model.password = itme["password"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(itme["password"]);
                model.Sex = itme["sex"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(itme["sex"]);
                model.Phone = itme["phone"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(itme["phone"]);
                model.Address = itme["address"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(itme["address"]);
                model.Bornday = itme["bornDay"].GetType() == typeof(System.DBNull) ? new Nullable<DateTime>() : Convert.ToDateTime(itme["bornDay"]);
                model.Headimgurl = itme["headImgURL"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(itme["headImgURL"]);
                model.Schoolid = itme["schoolId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(itme["schoolId"]);
                model.Type = itme["type"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(itme["type"]);
                model.status = itme["status"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(itme["status"]);
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 分页查询幼儿园班级记录
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="youErYuanId">幼儿园Id</param>
        /// <param name="total">条件查询总记录数</param>
        /// <returns></returns>
        public object TeacherPageList(int pageIndex, int pageSize, string youErYuanId, out int total)
        {
            string where = "";
            if (string.IsNullOrEmpty(youErYuanId) == false)
            {
                youErYuanId = SqlFilter.Filter(youErYuanId);
                where = " schoolid = '" + youErYuanId + "' and type=1";
            }
            DataTable dt = SQLHepler.GetPaging("[teacherName] asc", "[T_Teacher]", where, pageIndex, pageSize, out total);
            return dt;
        }

        public int Add(Teacher model)
        {
            string sql = "INSERT INTO [dbo].[T_Teacher]([id],[teacherName],[kahao],[password],[sex],[phone],[address],[bornDay],[schoolId],[type],[status]) VALUES(@id,@teacherName,@kahao,@password,@sex,@phone,@address,@bornDay,@schoolId,'1',@status)";
            object teacherName = model.Teachername;
            object kahao = model.Kahao;
            object sex = model.Sex;
            object address = model.Address;
            object bornday = model.Bornday;
            object schoolid = model.Schoolid;
            object status = model.status;
            object password = model.password;
            if (model.Teachername == null)
            {
                teacherName = DBNull.Value;
            }
            if (model.Kahao == null)
            {
                kahao = DBNull.Value;
            }
            if (model.Sex == null)
            {
                sex = DBNull.Value;
            }
            if (model.Address == null)
            {
                address = DBNull.Value;
            }
            if (model.Bornday == null)
            {
                bornday = DBNull.Value;
            }
            if (model.Schoolid == null)
            {
                schoolid = DBNull.Value;
            }
            if (model.status == null)
            {
                status = DBNull.Value;
            }
            if (model.password == null)
            {
                password = DBNull.Value;
            }
            SqlParameter[] parames ={
                new SqlParameter("@id",model.Id),
                new SqlParameter("@Teachername",teacherName),
                new SqlParameter("@kahao",kahao),
                new SqlParameter("@sex",sex),
                new SqlParameter("@phone",model.Phone),
                new SqlParameter("@address",address),
                new SqlParameter("@bornDay",bornday),
                new SqlParameter("@schoolId",schoolid),
                new SqlParameter("@status",status),
                new SqlParameter("@password",password)
            };
            string sql_relation = "INSERT INTO [dbo].[T_TeacherClassRelation]([id],[classId],[teacherId]) VALUES(NEWID(),@classId,@teacherId)";
            List<object[]> list = new List<object[]>();
            object[] teacher =
            {
                sql,parames
            };
            //关系表
            if (string.IsNullOrEmpty(model.Classid) == false && string.IsNullOrWhiteSpace(model.Classid) == false)
            {
                string[] classIds = model.Classid.Split(',');
                foreach (var item in classIds)
                {
                    SqlParameter[] parmaes2 =
                    {
                        new SqlParameter("@classId",item),
                        new SqlParameter("@teacherId",model.Id),
                    };
                    object[] classRelation =
                    {
                        sql_relation,parmaes2
                    };
                    list.Add(classRelation);
                }
            }

            list.Add(teacher);
            int count = SQLHepler.ExecSqlTransacation(list);
            return count;
        }

        public int Update(Teacher model)
        {
            List<object[]> cmdlist = new List<object[]>();
            object teacherName = model.Teachername;
            object kahao = model.Kahao;
            object sex = model.Sex;
            object address = model.Address;
            object bornday = model.Bornday;
            object schoolid = model.Schoolid;
            object status = model.status;
            object password = model.password;
            if (model.Teachername == null)
            {
                teacherName = DBNull.Value;
            }
            if (model.Kahao == null)
            {
                kahao = DBNull.Value;
            }
            if (model.Sex == null)
            {
                sex = DBNull.Value;
            }
            if (model.Address == null)
            {
                address = DBNull.Value;
            }
            if (model.Bornday == null)
            {
                bornday = DBNull.Value;
            }
            if (model.Schoolid == null)
            {
                schoolid = DBNull.Value;
            }
            if (model.status == null)
            {
                status = DBNull.Value;
            }
            if (model.password == null)
            {
                password = DBNull.Value;
            }
            string sql = "UPDATE [dbo].[T_Teacher] SET [teacherName] = @teacherName,[kahao] = @kahao,[password] = @password,[sex] = @sex,[phone] = @phone,[address] = @address,[bornDay] = @bornDay,[schoolId] = @schoolId,[status] = @status WHERE [id] = @id";
            SqlParameter[] parames ={
                new SqlParameter("@teacherName",teacherName),
                new SqlParameter("@kahao",kahao),
                new SqlParameter("@sex",sex),
                new SqlParameter("@phone",model.Phone),
                new SqlParameter("@address",address),
                new SqlParameter("@bornDay",bornday),
                new SqlParameter("@schoolId",schoolid),
                new SqlParameter("@status",status),
                new SqlParameter("@password",password),
                new SqlParameter("@id",model.Id)
            };
            object[] update_teacher =
           {
                sql,parames
            };
            //先删除关系表
            string sql2 = "delete from [dbo].[T_TeacherClassRelation] where [teacherId]=@teacherId";
            object[] del_teacher_realation =
            {
                sql2,new SqlParameter[] { new SqlParameter("@teacherId",model.Id) }
            };
            //加载命令
            cmdlist.Add(del_teacher_realation);
            //添加关系表
            string sql3 = "INSERT INTO [dbo].[T_TeacherClassRelation]([id],[classId],[teacherId]) VALUES(NEWID(),@classId,@teacherId)";
            if (string.IsNullOrEmpty(model.Classid) == false && string.IsNullOrWhiteSpace(model.Classid) == false)
            {
                string[] classIds = model.Classid.Split(',');
                foreach (var item in classIds)
                {
                    SqlParameter[] parmaes =
                    {
                        new SqlParameter("@classId",item),
                        new SqlParameter("@teacherId",model.Id),
                    };
                    object[] add_teacher_realation =
                    {
                        sql3,parmaes
                    };
                    cmdlist.Add(add_teacher_realation);
                }
            }
            cmdlist.Add(update_teacher);
            return SQLHepler.ExecSqlTransacation(cmdlist);
        }

        /// <summary>
        /// 班级查询
        /// </summary>
        /// <param name="schoolid"></param>
        /// <returns></returns>
        public List<TClass> SelectClassForSchoolid(string schoolid)
        {
            string sql = "select id,className from dbo.T_Class where schoolid=@schoolid";
            DataTable dt = SQLHepler.GetDataTable(sql, new SqlParameter("@schoolid", schoolid));
            List<TClass> classlist = new List<TClass>();
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var item = dt.Rows[i];
                    TClass model = new TClass();
                    model.Id = item["id"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["id"]);
                    model.Classname = item["className"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["className"]);
                    classlist.Add(model);
                }
            }
            return classlist;
        }

        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="tid"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public int UpdatePassword(string tid, string password)
        {
            string sql = "update T_Teacher set ,password='" + password + "' where id='" + tid + "'";
            int count = SQLHepler.ExecuteSql(sql, null);
            return count;
        }

        public int UpdateClass(string tid, string cid)
        {
            return 0;
        }

        public int DeleteTeacher(string teacherId)
        {
            string sql = "delete from [dbo].[T_Teacher] where [id]=@teacherId";
            return SQLHepler.ExecuteSql(sql, new SqlParameter("@teacherId", teacherId));
        }

        /// <summary>
        /// 根据教师id查询绑定的班级列表
        /// </summary>
        /// <param name="teacherId"></param>
        /// <returns></returns>
        public List<TClass> GetTeacherReationList(string teacherId)
        {
            List<TClass> list = new List<TClass>();
            string sql = "SELECT [id],[classId],[teacherId] FROM [dbo].[T_TeacherClassRelation] where [teacherId]=@teacherId ";
            DataTable dt = SQLHepler.GetDataTable(sql, new SqlParameter("@teacherId", teacherId));
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var item = dt.Rows[i];
                    TClass model = new TClass();
                    model.Id = item["classId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["classId"]);
                    list.Add(model);
                }
            }
            return list;
        }

        /// <summary>
        /// 根据主键，修改教师手机号、密码、姓名
        /// </summary>
        /// <param name="phone"></param>
        /// <param name="pwd"></param>
        /// <param name="name"></param>
        /// <param name="teacherId"></param>
        /// <returns></returns>
        public int UpdateTeacher(string phone, string pwd, string name, string teacherId)
        {
            string sql = "UPDATE [dbo].[T_Teacher] SET [phone] =@phone,[teacherName] =@teacherName,[password] =@password  WHERE [id] =@id";
            pwd = DESencodingTxt.DESencrypt(pwd);
            SqlParameter[] parames = {
                new SqlParameter("@phone",phone),
                new SqlParameter("@teacherName",name),
                new SqlParameter("@id",teacherId),
                new SqlParameter("@password",pwd),
            };
            return SQLHepler.ExecuteSql(sql, parames);
        }
    }
}
